﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'P_Rpt_UserSessions')
	BEGIN
		DROP  Procedure  [esf_sso].P_Rpt_UserSessions
	END

GO


CREATE Procedure [esf_sso].P_Rpt_UserSessions
	@pUserAlias VARCHAR (MAX) = NULL
	,@pStartDate DATETIME = NULL
	,@pEndDate DATETIME = NULL
AS
	
	SELECT
		X.*
	FROM 
		(SELECT
			VU.Usr_Alias
			,VU.Usr_Per_cal_FullName
			,VU.Usr_Per_Ent_Name
			,VU.Usr_Per_Email
			,VU.Usr_Per_IT_Code
			,VU.Usr_Per_IdentificationNumber
			,MAX (USL.StartDateTime) Usr_LastStartDateTime
			,MAX (USL.UserHostAddress) Usr_LastUserHostAddress
		FROM 
			esf_sso.VUser VU
		INNER JOIN
			esf_sso.UserSessionLog USL ON
				USL.UserPersonID = VU.Usr_Per_ID
		WHERE
			(@pUserAlias IS NULL 
				OR VU.Usr_Alias LIKE @pUserAlias
				OR VU.Usr_Per_cal_FullName LIKE @pUserAlias
				OR VU.Usr_Per_IdentificationNumber LIKE @pUserAlias)
			AND (@pStartDate IS NULL OR USL.StartDateTime >= @pStartDate)
			AND (@pEndDate IS NULL OR USL.EndDateTime <= @pEndDate)		
		GROUP BY
			VU.Usr_Alias
			,VU.Usr_Per_cal_FullName
			,VU.Usr_Per_Ent_Name
			,VU.Usr_Per_Email
			,VU.Usr_Per_IT_Code
			,VU.Usr_Per_IdentificationNumber
	) X
	ORDER BY
		X.Usr_LastStartDateTime DESC
	